﻿Imports Microsoft.VisualBasic
Imports System.Collections.Generic

Imports System.Web.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions

Public Class clsData

    'Public Shared Connections As New Dictionary(Of Transaction, Dictionary(Of String, SqlConnection))
    Public Shared Connections As New Dictionary(Of Transaction, Dictionary(Of String, ForSQL2000SinglePhaseNotification))

    Public CommandTimeOut As Integer = 6000
    ''' <summary>
    ''' 連接SQL 2000若使用TransactionScpe會強迫選擇 MTC resource manager
    ''' 此處設定 Enlist=false, 在之後的GetConn時自動做local transaction
    ''' </summary>
    ''' <remarks></remarks>
    Private connStr As String = clsData.GetConnectionString()

    Public Shared Function GetConnectionString() As String
        Return WebConfigurationManager.ConnectionStrings("connStr").ConnectionString + ";Enlist=false;"
    End Function

    Public Sub New()

    End Sub

    Public Connection As SqlConnection

    Public Sub New(ByVal connection As SqlConnection)
        Me.Connection = connection
    End Sub

    Public Sub New(ByVal connectionStringName As String)
        Me.connStr = WebConfigurationManager.ConnectionStrings(connectionStringName).ConnectionString
    End Sub

    Public Shared Function ChangeDateTime(ByVal strDateTime As String) As String
        Dim rtnValue As String
        If strDateTime <> "" Then
            rtnValue = CType(strDateTime, DateTime).ToString("yyyy/MM/dd")
        Else
            rtnValue = ""
        End If

        Return rtnValue
    End Function

    'Select 取回一個DataSet    
    Public Function GetData(ByVal strSql As String, ByVal params() As SqlParameter, _
                    ByVal CmdType As CommandType, ByVal strTable As String) As DataSet

        Dim cmd As New SqlCommand(strSql)

        cmd.CommandType = CmdType

        If Not params Is Nothing Then
            Dim param As SqlParameter

            For Each param In params
                cmd.Parameters.Add(param)
            Next
        End If

        Return Me.GetData(cmd, strTable)
    End Function

    Public Function GetData(ByVal cmd As SqlCommand, ByVal strTable As String) As DataSet

        Dim Conn As SqlConnection = GetConn(connStr)

        cmd.Connection = Conn
        cmd.CommandTimeout = Me.CommandTimeOut
        cmd.Transaction = GetTransaction(connStr)

        Dim Da As New SqlDataAdapter
        'Da = New SqlDataAdapter(strSql, Conn)

        Da.SelectCommand = cmd
        'Da.SelectCommand.CommandType = CmdType
        'Da.SelectCommand.Transaction = GetTransaction(connStr)

        Dim Ds As New DataSet

        Try
            Da.Fill(Ds, strTable)
        Catch ex As Exception
            MyObj.WriteLog(cmd.CommandText)
            For i As Integer = 0 To cmd.Parameters.Count - 1
                MyObj.WriteLog("@" & cmd.Parameters.Item(i).ParameterName & "=" & cmd.Parameters.Item(i).Value)
            Next
            Throw ex
        Finally
            CloseConn(Conn)
        End Try

        Return Ds
    End Function

    Public Function GetData(ByVal strSql As String) As DataTable
        Return Me.GetData(strSql, Nothing, CommandType.Text, "dt").Tables(0)
    End Function

    Public Function GetRow(ByVal strSql As String, ByVal params As Dictionary(Of String, Object)) As DataRow
        Dim dt As DataTable = GetData(strSql, params)

        If dt.Rows.Count > 0 Then
            Return GetData(strSql, params).Rows(0)
        Else
            Return Nothing
        End If

    End Function

    Public Function GetData(ByVal strSql As String, ByVal params As Dictionary(Of String, Object)) As DataTable
        Dim temp_list As New List(Of SqlParameter)

        For Each key As String In params.Keys
            temp_list.Add(New SqlParameter(key, params(key)))
        Next

        Return Me.GetData(strSql, temp_list.ToArray, CommandType.Text, "dt").Tables(0)
    End Function

    Public Function GetData(ByVal strSql As String, ByVal params() As SqlParameter) As DataTable
        Return Me.GetData(strSql, params, CommandType.Text, "dt").Tables(0)
    End Function

    Public Function GetData(ByVal strSql As StringBuilder) As DataTable
        Return Me.GetData(strSql.ToString())
    End Function

    Public Function GetData(ByVal builder As SqlStringBuilder) As DataTable
        Return GetData(builder.ToStringBuilder())
    End Function

    Public Function GetData(ByVal strSql As MyStringBuilder) As DataTable
        If strSql.Cmd Is Nothing Then
            Return Me.GetData(strSql.ToString())
        Else
            'strSql.Cmd.CommandType = CommandType.Text
            strSql.Cmd.CommandText = strSql.ToString()

            Return Me.GetData(strSql.Cmd, "dt").Tables(0)
        End If

    End Function

    Public Sub AccData(ByVal strSql As String)
        Me.AccData(strSql, Nothing, CommandType.Text)
    End Sub

    Public Sub AccData(ByVal strSql As StringBuilder)
        Me.AccData(strSql.ToString())
    End Sub

    Public Sub AccData(ByVal strSql As MyStringBuilder)
        If strSql.Cmd Is Nothing Then
            Me.AccData(strSql.ToString())
        Else
            'strSql.Cmd.CommandType = CommandType.Text
            strSql.Cmd.CommandText = strSql.ToString()
            Me.AccData(strSql.Cmd)
        End If

    End Sub

    Public Sub AccData(ByVal builder As SqlStringBuilder)
        Me.AccData(builder.ToStringBuilder())
    End Sub

    Public Shared Function GetData2(ByVal strSql As String, ByVal conn As SqlConnection) As DataTable
        Dim Comm As New SqlCommand(strSql, conn)
        Dim da As SqlDataAdapter = New SqlDataAdapter(strSql, conn)

        Dim ds As New DataSet
        da.Fill(ds, "temp")

        Return ds.Tables(0)
    End Function

    '處理資料如update、delete、insert
    Public Sub AccData(ByVal strSql As String, ByVal params As Dictionary(Of String, Object))
        Dim temp_list As New List(Of SqlParameter)

        For Each key As String In params.Keys
            temp_list.Add(New SqlParameter(key, params(key)))
        Next

        AccData(strSql, temp_list.ToArray, CommandType.Text)
    End Sub

    Public Sub AccData(ByVal strSql As String, ByVal params() As SqlParameter)
        AccData(strSql, params, CommandType.Text)
    End Sub

    Public Sub AccData(ByVal strSql As String, ByVal params() As SqlParameter, _
                    ByVal CmdType As CommandType)

        Dim Comm As SqlCommand
        Comm = New SqlCommand(strSql)
        Comm.CommandType = CmdType

        If Not params Is Nothing Then
            Dim param As SqlParameter
            For Each param In params
                Comm.Parameters.Add(param)
            Next
        End If

        Me.AccData(Comm)
    End Sub

    Public Sub AccData(ByVal comm As SqlCommand)
        Dim Conn As SqlConnection = GetConn(connStr)

        comm.Connection = Conn
        comm.CommandTimeout = Me.CommandTimeOut
        comm.Transaction = GetTransaction(connStr)

        Try
            MyObj.SetLog(comm.CommandText, comm.Parameters)
            'Conn.Open()
            comm.ExecuteNonQuery()
        Catch ex As Exception
            MyObj.WriteLog(comm.CommandText)
            For i As Integer = 0 To comm.Parameters.Count - 1
                MyObj.WriteLog("@" & comm.Parameters.Item(i).ParameterName & "=" & comm.Parameters.Item(i).Value)
            Next
            Throw ex
        Finally
            CloseConn(Conn)
        End Try
    End Sub

    Public Shared Sub AccData(ByVal strSql As String, ByVal conn As SqlConnection)
        Dim Comm As New SqlCommand(strSql, conn)
        Comm.ExecuteNonQuery()
        MyObj.SetLog(Comm.CommandText)
    End Sub

    'Select 傳回一個值
    Public Function GetSingleData(ByVal strSql As String, ByVal params As Dictionary(Of String, Object)) As Object
        Dim temp_list As New List(Of SqlParameter)

        For Each key As String In params.Keys
            temp_list.Add(New SqlParameter(key, params(key)))
        Next

        Return Me.GetSingleData(strSql, temp_list.ToArray, CommandType.Text)
    End Function

    Public Function GetSingleData(ByVal strSql As String, Optional ByVal params() As SqlParameter = Nothing, _
                    Optional ByVal CmdType As CommandType = CommandType.Text) As Object
        'Dim Conn As SqlConnection = New SqlConnection(connStr)
        Dim Conn As SqlConnection = GetConn(connStr)
        Dim Comm As SqlCommand
        Comm = New SqlCommand(strSql, Conn)
        Comm.CommandType = CmdType
        Comm.CommandTimeout = Me.CommandTimeOut

        If Not params Is Nothing Then
            Dim param As SqlParameter
            For Each param In params
                Comm.Parameters.Add(param)
            Next
        End If

        Try
            'Conn.Open()
            Dim strValue As Object = Comm.ExecuteScalar()
            Return strValue
        Catch ex As Exception
            Throw ex
        Finally
            CloseConn(Conn)
        End Try
    End Function

    Public Function GetConn() As SqlConnection
        Return GetConn(connStr)
    End Function

    Public Function GetTransaction(ByVal connStr As String) As SqlTransaction
        Dim current As Transaction = Transaction.Current

        If (current Is Nothing) Then
            Return Nothing
        Else
            Dim dictionary As Dictionary(Of String, ForSQL2000SinglePhaseNotification)
            Dim notification As ForSQL2000SinglePhaseNotification

            Connections.TryGetValue(current, dictionary)
            dictionary.TryGetValue(connStr, notification)

            Return notification.Transaction
        End If

    End Function

    Public Function GetConn(ByVal connStr As String) As SqlConnection
        'Dim dictionary As Dictionary(Of String, SqlConnection)
        Dim dictionary As Dictionary(Of String, ForSQL2000SinglePhaseNotification)
        'Dim newOpenConnection As SqlConnection
        Dim newOpenConnection As ForSQL2000SinglePhaseNotification

        Dim current As Transaction = Transaction.Current

        If (current Is Nothing) Then
            If Me.Connection Is Nothing Then
                Return NewConn(connStr)
            Else
                Return Me.Connection
            End If
        Else
            Connections.TryGetValue(current, dictionary)
            If (Not dictionary Is Nothing) Then
                dictionary.TryGetValue(connStr, newOpenConnection)
                If (Not newOpenConnection Is Nothing) Then
                    Return newOpenConnection.Connection
                End If
            Else
                dictionary = New Dictionary(Of String, ForSQL2000SinglePhaseNotification)
                SyncLock Connections
                    Connections.Add(current, dictionary)
                End SyncLock
            End If
        End If

        If dictionary.ContainsKey(connStr) Then
            Return dictionary.Item(connStr).Connection
        Else
            dictionary.Add(connStr, New ForSQL2000SinglePhaseNotification(NewConn(connStr)))
            AddHandler current.TransactionCompleted, New TransactionCompletedEventHandler(AddressOf OnTransactionCompleted)

            current.EnlistPromotableSinglePhase(dictionary.Item(connStr))

            Return dictionary.Item(connStr).Connection
        End If

    End Function

    Public Function NewConn() As SqlConnection
        Return NewConn(connStr)
    End Function

    Public Function NewConn(ByVal connStr As String) As SqlConnection
        Dim conn As New SqlConnection(connStr)
        conn.Open()

        Return conn
    End Function

    Public Shared Function NewConnection() As SqlConnection
        Dim dat As New clsData

        Return dat.NewConn
    End Function

    Public Function CloseConn(ByVal conn As SqlConnection)
        Dim current As Transaction = Transaction.Current

        If (current Is Nothing And Me.Connection Is Nothing) Then
            conn.Close()
            conn.Dispose()
        End If

    End Function

    Private Shared Sub OnTransactionCompleted(ByVal sender As Object, ByVal e As TransactionEventArgs)
        Dim dictionary As Dictionary(Of String, ForSQL2000SinglePhaseNotification)
        Connections.TryGetValue(e.Transaction, dictionary)
        If (Not dictionary Is Nothing) Then
            SyncLock Connections
                Connections.Remove(e.Transaction)
            End SyncLock

            For Each o As ForSQL2000SinglePhaseNotification In dictionary.Values
                o.Dispose()
            Next
        End If
    End Sub

    Public Property ConnectionString() As String
        Get
            Return Me.connStr
        End Get
        Set(ByVal value As String)
            Me.connStr = value
        End Set
    End Property
End Class


''' <summary>
''' 解決以下問題
''' 連接SQL 2000若使用TransactionScpe會強迫選擇 MTC resource manager
''' 解決方式
''' 執行 local transaction (BeginTransaction)
''' </summary>
''' <remarks></remarks>
Public Class ForSQL2000SinglePhaseNotification
    Implements IPromotableSinglePhaseNotification, IDisposable

    Dim transactionField As SqlTransaction

    Public ReadOnly Property Transaction() As SqlTransaction
        Get
            Return Me.transactionField
        End Get
    End Property

    Dim connectionField As SqlConnection

    Public ReadOnly Property Connection() As SqlConnection
        Get
            Return Me.connectionField
        End Get
    End Property

    Public Sub New(ByVal connection As SqlConnection)
        Me.connectionField = connection
    End Sub

    Public Sub Initialize() Implements System.Transactions.IPromotableSinglePhaseNotification.Initialize
        Me.transactionField = Me.connectionField.BeginTransaction()
    End Sub

    Public Sub Rollback(ByVal singlePhaseEnlistment As System.Transactions.SinglePhaseEnlistment) Implements System.Transactions.IPromotableSinglePhaseNotification.Rollback
        Me.transactionField.Rollback()
        singlePhaseEnlistment.Done()
    End Sub

    Public Sub SinglePhaseCommit(ByVal singlePhaseEnlistment As System.Transactions.SinglePhaseEnlistment) Implements System.Transactions.IPromotableSinglePhaseNotification.SinglePhaseCommit
        Try
            Me.transactionField.Commit()
            singlePhaseEnlistment.Committed()
        Catch ex As Exception
            singlePhaseEnlistment.Aborted()
        End Try

    End Sub

    Public Function Promote() As Byte() Implements System.Transactions.ITransactionPromoter.Promote
        Throw New NotSupportedException("We do not support promotion.")
    End Function

    Public Sub Dispose() Implements IDisposable.Dispose

        Me.Connection.Close()
        Me.Connection.Dispose()
    End Sub
End Class
